<?php
jimport('joomla.application.component.model');
class svModelBangluong extends JModel
{
	//------------ tim kiem----------------------------------
	function getAllSunghiep($page)
	{
		if($page==0) $page=1;
		// so ban ghi hien thi tren mot trang la 10
		$numberspage=5;
		$start=($page-1)*$numberspage;
		$end=$numberspage;
		$query='SELECT  cb.cb_ten,cb.cb_id,cv.cv_ten,pb.pb_ten,lsn.lsn_ten,sn.* 
			    FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
			    		qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn 
			    WHERE   cb.cb_id = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		qlc.qlc_idchucvu=cv.cv_id 
				AND		lsn.lsn_id=sn.sn_idloaisunghiep
				AND 	sn.sn_idcanbo=cb.cb_id 
				ORDER BY cb.cb_id DESC LIMIT '.$start.','.$end;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllSunghiepCount()
	{
		$query="SELECT COUNT(sn_id) as count FROM qlcb_sunghiep WHERE 1";
		$result=$this->_loadObject($query);
		return $result->count;
	}
	//------------------------don vi quan ly---------------------------------------
	function getAllSunghiep1($iddonviquanly,$page)
	{
		if($page==0) $page=1;
		// so ban ghi hien thi tren mot trang la 10
		$numberspage=5;
		$start=($page-1)*$numberspage;
		$end=$numberspage;
		$query='SELECT  cb.cb_ten,cb.cb_id,cv.cv_ten,pb.pb_ten,lsn.lsn_ten,sn.*
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
		qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
		WHERE   cb.cb_id = qlc.qlc_idcanbo
		AND		qlc.qlc_idphongban=pb.pb_id
		AND		qlc.qlc_idchucvu=cv.cv_id
		AND		lsn.lsn_id=sn.sn_idloaisunghiep
		AND 	sn.sn_idcanbo=cb.cb_id
		AND 	pb.pb_iddonviquanly='.$iddonviquanly.' 
		ORDER BY cb.cb_id DESC LIMIT '.$start.','.$end;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllSunghiepCount1($iddonviquanly)
	{
		$query='SELECT  COUNT(sn.sn_id) as count 
				FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
						qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
				WHERE   cb.cb_id = qlc.qlc_idcanbo
				AND		qlc.qlc_idphongban=pb.pb_id
				AND		qlc.qlc_idchucvu=cv.cv_id
				AND		lsn.lsn_id=sn.sn_idloaisunghiep
				AND 	sn.sn_idcanbo=cb.cb_id
				AND 	pb.pb_iddonviquanly='.$iddonviquanly;
		$result=$this->_loadObject($query);
		return $result->count;
	}
	//--------------------------phong ban-----------------------------------
	function getAllSunghiep2($idphongban,$page)
	{
		if($page==0) $page=1;
		// so ban ghi hien thi tren mot trang la 10
		$numberspage=5;
		$start=($page-1)*$numberspage;
		$end=$numberspage;
		$query='SELECT  cb.cb_ten,cb.cb_id,cv.cv_ten,pb.pb_ten,lsn.lsn_ten,sn.*
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
		qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
		WHERE   cb.cb_id = qlc.qlc_idcanbo
		AND		qlc.qlc_idphongban=pb.pb_id
		AND		qlc.qlc_idchucvu=cv.cv_id
		AND		lsn.lsn_id=sn.sn_idloaisunghiep
		AND 	sn.sn_idcanbo=cb.cb_id
		AND 	pb.pb_id='.$idphongban.' 
		ORDER BY cb.cb_id DESC LIMIT '.$start.','.$end;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllSunghiepCount2($idphongban)
	{
		$query='SELECT  COUNT(sn.sn_id) as count 
				FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
						qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
				WHERE   cb.cb_id = qlc.qlc_idcanbo
				AND		qlc.qlc_idphongban=pb.pb_id
				AND		qlc.qlc_idchucvu=cv.cv_id
				AND		lsn.lsn_id=sn.sn_idloaisunghiep
				AND 	sn.sn_idcanbo=cb.cb_id
				AND 	pb.pb_id='.$idphongban;
		$result=$this->_loadObject($query);
		return $result->count;
	}
	//------------------- can bo ------------------------------------
	function getAllSunghiep3($idcanbo,$page)
	{
		if($page==0) $page=1;
		// so ban ghi hien thi tren mot trang la 10
		$numberspage=5;
		$start=($page-1)*$numberspage;
		$end=$numberspage;
		$query='SELECT  cb.cb_ten,cb.cb_id,cv.cv_ten,pb.pb_ten,lsn.lsn_ten,sn.*
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
		qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
		WHERE   cb.cb_id = qlc.qlc_idcanbo 
		AND		qlc.qlc_idphongban=pb.pb_id 
		AND		qlc.qlc_idchucvu=cv.cv_id 
		AND		lsn.lsn_id=sn.sn_idloaisunghiep 
		AND 	sn.sn_idcanbo=cb.cb_id 
		AND 	cb.cb_id='.$idcanbo.' 
		ORDER BY cb.cb_id DESC LIMIT '.$start.','.$end;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllSunghiepCount3($idcanbo)
	{
		$query='SELECT  COUNT(sn.sn_id) as count 
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
		qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
		WHERE   cb.cb_id = qlc.qlc_idcanbo
		AND		qlc.qlc_idphongban=pb.pb_id
		AND		qlc.qlc_idchucvu=cv.cv_id
		AND		lsn.lsn_id=sn.sn_idloaisunghiep
		AND 	sn.sn_idcanbo=cb.cb_id
		AND 	cb.cb_id='.$idcanbo;
		$result=$this->_loadObject($query);
		return $result->count;
	}
	//------------------------------------------------------------------
	function getAllSunghiep4($idloaisunghiep,$page)
	{
		if($page==0) $page=1;
		// so ban ghi hien thi tren mot trang la 10
		$numberspage=5;
		$start=($page-1)*$numberspage;
		$end=$numberspage;
		$query='SELECT  cb.cb_ten,cb.cb_id,cv.cv_ten,pb.pb_ten,lsn.lsn_ten,sn.*
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
		qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
		WHERE   cb.cb_id = qlc.qlc_idcanbo
		AND		qlc.qlc_idphongban=pb.pb_id
		AND		qlc.qlc_idchucvu=cv.cv_id
		AND		lsn.lsn_id=sn.sn_idloaisunghiep
		AND 	sn.sn_idcanbo=cb.cb_id
		AND 	lsn.lsn_id='.$idloaisunghiep.'
		ORDER BY cb.cb_id DESC LIMIT '.$start.','.$end;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllSunghiepCount4($idloaisunghiep)
	{
		$query='SELECT  COUNT(sn.sn_id) as count
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
		qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
		WHERE   cb.cb_id = qlc.qlc_idcanbo
		AND		qlc.qlc_idphongban=pb.pb_id
		AND		qlc.qlc_idchucvu=cv.cv_id
		AND		lsn.lsn_id=sn.sn_idloaisunghiep
		AND 	sn.sn_idcanbo=cb.cb_id
		AND 	lsn.lsn_id='.$idloaisunghiep;
		$result=$this->_loadObject($query);
		return $result->count;
	}
	//=====================================================================
	function getSunghiepByID($idsunghiep)
	{
		$query='SELECT  cb.cb_ten,cb.cb_id,cv.cv_ten,pb.pb_ten,lsn.lsn_id,lsn.lsn_ten,sn.*
			FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv,
					qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
			WHERE   cb.cb_id = qlc.qlc_idcanbo 
			AND		qlc.qlc_idphongban=pb.pb_id 
			AND		qlc.qlc_idchucvu=cv.cv_id 
			AND		lsn.lsn_id=sn.sn_idloaisunghiep 
			AND 	sn.sn_idcanbo=cb.cb_id 
			AND 	sn.sn_id='.$idsunghiep;
		$result=$this->_loadObject($query);
		return $result;
	}
	function getAllLoaisunghiep()
	{
		$query='SELECT lsn_id,lsn_ten
				FROM qlcb_loaisunghiep
				WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllCanbo()
	{
		$query='SELECT cb_id,cb_ten
		FROM qlcb_canbo
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getphongbanList($iddonviquanly)
	{
		$cmd=($iddonviquanly=='')? '1' : 'pb_iddonviquanly='.$iddonviquanly;
		$query='SELECT pb_id,pb_ten
		FROM qlcb_phongban
		WHERE '.$cmd;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getcanboList($idphongban)
	{
		$cmd=($idphongban=='')? '1' : 'qlc.qlc_idphongban='.$idphongban;
		$query='SELECT cb.cb_id,cb.cb_ten
		FROM qlcb_canbo as cb,qlcb_quanlychung as qlc
		WHERE qlc.qlc_idcanbo=cb.cb_id AND '.$cmd;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getdonviList()
	{
		$query='SELECT dvql_id,dvql_ten
		FROM qlcb_donviquanly
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	//----------------------------them ,sua ,xoa -------------------------------
	function addNewSunghiep($idcanbo,$idloaisunghiep,$tieude,$diadiem,$batdau,$ketthuc,$thanhtich,$mieuta,$tinhtrang)
	{
		$query="INSERT INTO qlcb_sunghiep(sn_idcanbo,sn_idloaisunghiep,sn_tieude,sn_diadiem,sn_batdau,sn_ketthuc,sn_thanhtich,sn_mieuta,sn_tinhtrang)
		VALUES('".$idcanbo."','".$idloaisunghiep."','".$tieude."','".$diadiem."','".$batdau."',
		'".$ketthuc."','".$thanhtich."','".$mieuta."','".$tinhtrang."')";
		$message='Thêm mới 1 item ';
		$result=$this->_Execute($query, $message);
		return '<b style="font-size:18px; color:blue;">'.$result.'</b>';
	}
	// sua
	function editSunghiep($idcanbo,$idloaisunghiep,$tieude,$diadiem,$batdau,$ketthuc,$thanhtich,$mieuta,$tinhtrang,$idsunghiep)
	{
		$query="UPDATE qlcb_sunghiep 
				SET sn_idcanbo='".$idcanbo."', sn_idloaisunghiep='".$idloaisunghiep."', sn_tieude='".$tieude."',
				 sn_diadiem='".$diadiem."', sn_batdau='".$batdau."', sn_ketthuc='".$ketthuc."',
				 sn_thanhtich='".$thanhtich."',	 sn_mieuta='".$mieuta."', sn_tinhtrang='".$tinhtrang."'
	   		    WHERE sn_id=".$idsunghiep;
		$message='Chỉnh sửa ';
		$result=$this->_Execute($query, $message);
		return '<b style="font-size:18px; color:blue;">'.$result.'</b>';
	}
	function delChucdanh($idchucvu)
	{
		$query="DELETE FROM qlcb_chucdanh WHERE cd_id=".$idchucvu;
		$message='Xóa 1 item ';
		$result=$this->_Execute($query, $message);
		return $result;	
	}
	// cac ham phuc vu truy van CSDL
	function _loadObject($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObject();
		return $result;
	}
	function _loadObjectList($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObjectList();
		return $result;
	}
	function _Execute($query,$message)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		try {
			$db->query();
			return $message." thành công ";
		}
		catch (Exception $e){
			return $message." Không thành công! Vui lòng thử lại.";
		}
	}
}
?>